package org.just.dusin.ooii.database;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;


import org.dusin.webcoal.exception.CoalException;
import org.just.dusin.ooii.util.DateUtil;

public class FaultDao {

    private Connection conn = null;
    public int size;

    public FaultDao(Connection conn) {
        this.conn = conn;
    }

    public boolean doCreate(FaultBean faultBean) throws Exception {
        boolean flag = false;
        PreparedStatement pstmt = null;
        String sql = "INSERT INTO [fault]([orgCode],[faultType],[faultStatus],[startTime],stopTime,note)"
                + " VALUES (?,?,?,?,?,?)";
        try {
            pstmt = this.conn.prepareStatement(sql);
            pstmt.setString(1, faultBean.getOrgCode());
            pstmt.setString(2, faultBean.getFaultType());
            pstmt.setByte(3, faultBean.getFaultStatus());
            pstmt.setString(4, faultBean.getStartTime());
            pstmt.setString(5, faultBean.getStopTime());
            pstmt.setString(6, faultBean.getNote());

            int len = pstmt.executeUpdate();
            if (len > 0) {
                flag = true;
            }
        } catch (Exception e) {
            throw e;
        } finally {
            try {
                pstmt.close();
            } catch (Exception e) {
                throw e;
            }
        }
        return flag;
    }

    public boolean doUpdate(FaultBean faultBean) throws Exception {
        boolean flag = false;
        PreparedStatement pstmt = null;
        String sql = "UPDATE [fault] SET [orgcode]	 =?,[faulttype] = ? ,[faultstatus] =? ,[starttime] = ? ,[stoptime] = ? ,[note] =? WHERE id=?";
        try {
            pstmt = this.conn.prepareStatement(sql);

            pstmt.setString(1, faultBean.getOrgCode());
            pstmt.setString(2, faultBean.getFaultType());
            pstmt.setByte(3, faultBean.getFaultStatus());
            pstmt.setString(4, faultBean.getStartTime());
            pstmt.setString(5, faultBean.getStopTime());
            pstmt.setString(6, faultBean.getStopTime());

            pstmt.setLong(7, faultBean.getId());

            int len = pstmt.executeUpdate();
            if (len > 0) {
                flag = true;
            }
        } catch (Exception e) {
            throw e;
        } finally {
            try {
                pstmt.close();
            } catch (Exception e) {
                throw e;
            }
        }
        return flag;
    }

    public boolean doDelete(long pid) throws Exception {
        boolean flag = false;
        PreparedStatement pstmt = null;
        String sql = "DELETE FROM fault WHERE id=?";
        try {
            pstmt = this.conn.prepareStatement(sql);
            pstmt.setLong(1, pid);
            int len = pstmt.executeUpdate();
            if (len > 0) {
                flag = true;
            }
        } catch (Exception e) {
            throw e;
        } finally {
            try {
                pstmt.close();
            } catch (Exception e) {
                throw e;
            }
        }
        return flag;
    }

    public boolean doDelete(String[] pids) throws Exception {
        for (String pid : pids) {
            long id = Long.parseLong(pid);
            doDelete(id);
        }
        return true;
    }

    public FaultBean findById(long pid) throws Exception {
        FaultBean fault = null;
        PreparedStatement pstmt = null;
        String sql = "SELECT * FROM fault WHERE id=?";
        try {
            pstmt = this.conn.prepareStatement(sql);
            pstmt.setLong(1, pid);
            ResultSet rs = pstmt.executeQuery();
            if (rs.next()) { //
                fault = new FaultBean();
                fault.setId(rs.getLong(1));
                fault.setOrgCode(rs.getString(2));
                fault.setFaultType(rs.getString(3));
                fault.setFaultStatus(rs.getByte(4));
                Date startDate = rs.getDate(5);
                Date stopDate = rs.getDate(6);
                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:ss");
                String startTime = sdf.format(startDate);
                String stopTime = sdf.format(stopDate);
                fault.setStartTime(startTime);
                fault.setStopTime(stopTime);
                fault.setNote(rs.getString(7));
            }
        } catch (Exception e) {
            throw e;
        } finally {
            try {
                pstmt.close();
            } catch (Exception e) {
                throw e;
            }
        }
        return fault;
    }

    //选择最近异常未闭合的异常
    public FaultBean loadTheException(String ycType, String orgCode) throws Exception {
        FaultBean fault = null;
        //String sql = "select top 1 id,starttime,note from fault WHERE orgcode=? and faulttype=? and faultstatus=1 order by starttime desc"; //该语句可能会导致，旧的未闭合异常，影响新异常的闭合和开启
        String sql = "select top 1 id,starttime,note ,faultstatus from fault WHERE orgcode=? and faulttype=? order by starttime desc";   //改成查询最近的一次某类型异常，通过faultstatus判断是否闭合
        PreparedStatement pstmt = null;
        try {
            pstmt = this.conn.prepareStatement(sql);
            pstmt.setString(1, orgCode);
            pstmt.setString(2, ycType);
            ResultSet rs = pstmt.executeQuery();
            if (rs.next()) {
                fault = new FaultBean();
                fault.setId(rs.getLong(1));
                fault.setOrgCode(orgCode);
                fault.setFaultType(ycType);
                Date startDate = rs.getTimestamp(2);
                fault.setStartTime(DateUtil.get(startDate, "yyyy-MM-dd HH:mm"));
                fault.setNote(rs.getString(3));
                fault.setFaultStatus(rs.getByte(4));
            }

        } catch (Exception e) {
            throw e;
        } finally {
            try {
                pstmt.close();
            } catch (Exception e) {
                throw e;
            }
        }
        return fault;
    }

    public boolean updateByStatus(String status, String orgCode, Date time) throws Exception {
        boolean flag = false;
        PreparedStatement pstmt = null;
        int smyc = status.charAt(0) - '0';
        int jlyc = status.charAt(1) - '0';
        int wkyc = status.charAt(2) - '0';
        int txyc = status.charAt(3) - '0';
        int ztyc = status.charAt(4) - '0';
        int gdyc = status.charAt(5) - '0';
        String smType = "输煤异常";
        String jlType = "计量异常";
        String wkType = "外壳异常";
        String txType = "通信异常";
        String sbztType = "设备状态";
        String gdType = "供电异常";

        // yc值为2不更新，值为2表示不明类型
        if (smyc == 1 || smyc == 0) {
            updateSingleStatus(smType, smyc, orgCode, time);
        }
        if (jlyc == 1 || jlyc == 0) {
            updateSingleStatus(jlType, jlyc, orgCode, time);
        }
        if (wkyc == 1 || wkyc == 0) {
            updateSingleStatus(wkType, wkyc, orgCode, time);
        }
        if (txyc == 1 || txyc == 0) {
            updateSingleStatus(txType, txyc, orgCode, time);
        }
        if (ztyc == 1 || ztyc == 0) {
            updateSingleStatus(sbztType, ztyc, orgCode, time);
        }
        if (gdyc == 1 || gdyc == 0) {
            updateSingleStatus(gdType, gdyc, orgCode, time);
        }
        return flag;
    }

    private void updateSingleStatus(String ycType, int singleStatus, String orgCode, Date time) throws Exception {
        FaultBean fault = null;
        fault = loadTheException(ycType, orgCode);  //载入最近一次输煤异常
        if (singleStatus == 1) {
            if (fault == null || fault.getFaultStatus() == 2) { //如果原来没有相关异常，或已闭合则创建新的异常。
                fault = new FaultBean();
                fault.setOrgCode(orgCode);
                fault.setFaultStatus((byte) 1);   //1异常打开
                fault.setFaultType(ycType);
                fault.setNote("");
                fault.setStartTime(DateUtil.get(time, "yyyy-MM-dd HH:mm"));
                fault.setStopTime(null);
                doCreate(fault);
            }
        } else {

            if (fault != null && fault.getFaultStatus() == 1) {
                fault.setStopTime(DateUtil.get(time, "yyyy-MM-dd HH:mm"));//设置结束时间
                fault.setFaultStatus((byte) 2);  //异常闭合
                doUpdate(fault);
            }
        }
    }

    public List<FaultBean> findAll(String orgCode, int yearFrom, int monthFrom, int dayFrom, int yearTo, int monthTo, int dayTo, int beginIndex, int size) throws CoalException {
        List<FaultBean> all = new ArrayList<FaultBean>();
        PreparedStatement pstmt = null;
        PreparedStatement pstmtsize = null;
        String sqlsize = "select count(*) from fault where orgcode like '" + orgCode + "%' and starttime>=? and starttime<= ?";

        String sql = "select id ,orgCode,smallname ,faultType,faultStatus,startTime,stopTime,note FROM (select fault.id,fault.[orgcode],[smallname],faultType,faultStatus,startTime,stopTime,fault.note,row_number() over(order by fault.id) rn from fault  left join org  on fault.orgcode=org.orgcode where fault.orgcode like '" + orgCode + "%'  and starttime>=? and starttime<=?) t where rn >=? and rn<?";

        try {
            pstmtsize = this.conn.prepareStatement(sqlsize);


            String startTime = "" + yearFrom + "-" + monthFrom + "-" + dayFrom;
            String stopTime = "" + yearTo + "-" + monthTo + "-" + dayTo;
            pstmtsize.setString(1, startTime);
            pstmtsize.setString(2, stopTime);

            ResultSet rssize = pstmtsize.executeQuery(); //
            rssize.next();
            this.size = rssize.getInt(1);

            pstmt = this.conn.prepareStatement(sql);
            pstmt.setString(1, startTime);
            pstmt.setString(2, stopTime);
            pstmt.setInt(3, beginIndex + 1);
            pstmt.setInt(4, beginIndex + size + 1);
            ResultSet rs = pstmt.executeQuery(); //
            FaultBean fault = null;
            while (rs.next()) { //
                fault = new FaultBean();
                fault.setId(rs.getLong(1));
                fault.setOrgCode(rs.getString(2));
                fault.setSmallName(rs.getString(3));
                fault.setFaultType(rs.getString(4));
                fault.setFaultStatus(rs.getByte(5));
                Date startDate = rs.getTimestamp(6);
                Date stopDate = rs.getTimestamp(7);
                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm");
                startTime = sdf.format(startDate);
                stopTime = sdf.format(stopDate);
                fault.setStartTime(startTime);
                fault.setStopTime(stopTime);
                fault.setNote(rs.getString(8));
                all.add(fault); //
//System.out.println(all.size());
            }
        } catch (Exception e) {
            e.printStackTrace();
            throw new CoalException(e);
        } finally {
            try {
                pstmt.close();
            } catch (Exception e) {
                throw new CoalException(e);
            }
        }
        return all;
    }

    public List<FaultBean> findAll(String orgCode, int yearFrom, int monthFrom, int dayFrom, int yearTo, int monthTo, int dayTo) throws CoalException {
        List<FaultBean> all = new ArrayList<FaultBean>();
        PreparedStatement pstmt = null;
        String sql = "select fault.id,fault.[orgcode],[smallname],faultType,faultStatus,startTime,stopTime,fault.note from fault  left join org  on fault.orgcode=org.orgcode where fault.orgcode like '" + orgCode + "%'  and starttime>=? and starttime<=?";
        try {
            String startTime = "" + yearFrom + "-" + monthFrom + "-" + dayFrom;
            String stopTime = "" + yearTo + "-" + monthTo + "-" + dayTo;


            pstmt = this.conn.prepareStatement(sql);
            pstmt.setString(1, startTime);
            pstmt.setString(2, stopTime);
            ResultSet rs = pstmt.executeQuery(); //
            FaultBean fault = null;
            while (rs.next()) { //
                fault = new FaultBean();
                fault.setId(rs.getLong(1));
                fault.setOrgCode(rs.getString(2));
                fault.setSmallName(rs.getString(3));
                fault.setFaultType(rs.getString(4));
                fault.setFaultStatus(rs.getByte(5));
                Date startDate = rs.getDate(6);
                Date stopDate = rs.getDate(7);
                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm");
                startTime = sdf.format(startDate);
                stopTime = sdf.format(stopDate);
                fault.setStartTime(startTime);
                fault.setStopTime(stopTime);
                fault.setNote(rs.getString(8));
                all.add(fault); //
//System.out.println(all.size());
            }
        } catch (Exception e) {
            e.printStackTrace();
            throw new CoalException(e);
        } finally {
            try {
                pstmt.close();
            } catch (Exception e) {
                throw new CoalException(e);
            }
        }
        return all;
    }

    public int getTotalSize() {
        return size;
    }

    /*public boolean update(String status, String orgCode)throws CoalException {
    
    int smyc = status.charAt(0)-'0';
    int jlyc = status.charAt(1)-'0';
    int wkyc = status.charAt(2)-'0';
    int txyc = status.charAt(3)-'0';        
    }*/
}
